package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Book;
import model.Product;
import util.DBConnection;

public class ProductDAOImp implements ProductDAO {
	private final String FIND_by_id = "select * from d_product p left join d_book b on b.id=p.id where p.id=?";
	private final String FIND_by_Hod = "select sum(product_num) as num,p.*,b.* from d_item i join d_product p"
			+ " on i.product_id=p.id join d_book b on i.product_id=b.id GROUP BY product_id ORDER BY num desc limit 0,?";
	private final String FIND_by_new = "select p.*,b.* from d_product p join d_book b on p.id=b.id order by p.add_time desc limit 0,?;";

	public Book findById(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		Book book = null;
		ResultSet rst = null;
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_by_id);
			prep.setInt(1, id);
			rst = prep.executeQuery();
			if (rst.next()) {
				book = new Book();
				book.setAuthor(rst.getString("author"));
				book.setAuthorSummary(rst.getString("author_summary"));
				book.setCatalogue(rst.getString("catalogue"));
				book.setId(rst.getInt("id"));
				book.setIsbn(rst.getString("isbn"));
				book.setPrintNumber(rst.getString("print_number"));
				book.setPrintDt(rst.getLong("print_time"));
				book.setPublishing(rst.getString("publishing"));
				book.setTotalPage(rst.getString("total_page"));
				book.setPublishDt(rst.getLong("publish_time"));
				book.setWhichEdtion(rst.getString("which_edtion"));
				book.setWordNumber(rst.getString("word_number"));

				book.setProductName(rst.getString("product_name"));
				book.setAddTime(rst.getLong("add_time"));
				book.setFixedPrice(rst.getDouble("fixed_price"));
				book.setDangPrice(rst.getDouble("dang_price"));
				book.setKeywords(rst.getString("keywords"));
				book.setHasDeleted(rst.getInt("has_deleted"));
				book.setProductPic(rst.getString("product_pic"));
				book.setDescription(rst.getString("description"));
			}
		} finally {
			DBConnection.close(prep, conn);
		}
		return book;
	}

	public List<Product> findByHot(int num) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;

		Book book = null;
		List<Product> pr = new ArrayList<Product>();
		try {
			book = new Book();
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_by_Hod);
			prep.setInt(1, num);
			rst = prep.executeQuery();
			while (rst.next()) {
				book = new Book();
				book.setAuthor(rst.getString("author"));
				book.setAuthorSummary(rst.getString("author_summary"));
				book.setCatalogue(rst.getString("catalogue"));
				book.setId(rst.getInt("id"));
				book.setIsbn(rst.getString("isbn"));
				book.setPrintNumber(rst.getString("print_number"));
				book.setPrintDt(rst.getLong("print_time"));
				book.setPublishing(rst.getString("publishing"));
				book.setTotalPage(rst.getString("total_page"));
				book.setPublishDt(rst.getLong("publish_time"));
				book.setWhichEdtion(rst.getString("which_edtion"));
				book.setWordNumber(rst.getString("word_number"));

				book.setProductName(rst.getString("product_name"));
				book.setAddTime(rst.getLong("add_time"));
				book.setFixedPrice(rst.getDouble("fixed_price"));
				book.setDangPrice(rst.getDouble("dang_price"));
				book.setKeywords(rst.getString("keywords"));
				book.setHasDeleted(rst.getInt("has_deleted"));
				book.setProductPic(rst.getString("product_pic"));
				book.setDescription(rst.getString("description"));

				pr.add(book);
			}
		} finally {
			DBConnection.close(rst, prep, conn);
		}
		return pr;
	}

	@Override
	public List<Product> findByNew(int id) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		ResultSet rst = null;
		Book book = null;
		List<Product> pr = new ArrayList<Product>();
		try {
			book = new Book();
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_by_new);
			prep.setInt(1, id);
			rst = prep.executeQuery();
			while (rst.next()) {
				book = new Book();
				book.setAuthor(rst.getString("author"));
				book.setAuthorSummary(rst.getString("author_summary"));
				book.setCatalogue(rst.getString("catalogue"));
				book.setId(rst.getInt("id"));
				book.setIsbn(rst.getString("isbn"));
				book.setPrintNumber(rst.getString("print_number"));
				book.setPrintDt(rst.getLong("print_time"));
				book.setPublishing(rst.getString("publishing"));
				book.setTotalPage(rst.getString("total_page"));
				book.setPublishDt(rst.getLong("publish_time"));
				book.setWhichEdtion(rst.getString("which_edtion"));
				book.setWordNumber(rst.getString("word_number"));

				book.setProductName(rst.getString("product_name"));
				book.setAddTime(rst.getLong("add_time"));
				book.setFixedPrice(rst.getDouble("fixed_price"));
				book.setDangPrice(rst.getDouble("dang_price"));
				book.setKeywords(rst.getString("keywords"));
				book.setHasDeleted(rst.getInt("has_deleted"));
				book.setProductPic(rst.getString("product_pic"));
				book.setDescription(rst.getString("description"));
				pr.add(book);

			}
		} finally {
			DBConnection.close(rst, prep, conn);
		}
		return pr;
	}
}